package com.ryskoo.customer.customer.repository;

import com.ryskoo.customer.customer.domain.CustomerPO;
import com.ryskoo.customer.customeraccount.domain.CustomerAccountPO;
import com.ryskoo.framework.core.bean.QueryMessageDTO;
import com.ryskoo.framework.core.repository.BasicJPAImpl;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Shawn
 */
@Repository
public class CustomerJPAImpl extends BasicJPAImpl<CustomerPO> implements ICustomerRepository {

    /**
     * 商户后台 - 冻结/激活 会员
     *
     * @param idList
     * @param activeState
     * @return
     */
    @Override
    public void handleFreeze(List<String> idList, String activeState) {
        String hql = "update CustomerPO o set o.activeState = :activeState where o.uuid in (:uuids)";

        Map<String, Object> mapParams = new HashMap<String, Object>();
        mapParams.put("uuids", idList.toArray());
        mapParams.put("activeState", activeState);

        this.exeUpdate(hql, mapParams);
    }

    /**
     * 查询会员的用户名/手机号/编号 是否已存在
     *
     * @param loginName
     * @return
     */
    @Override
    public boolean checkLoginNameExist(String loginName, String entityNo, String uuid) {
        String hql = "select o.uuid from CustomerPO o where 1=1 ";
        if (StringUtils.isNotEmpty(loginName)) {
            hql += " and (o.loginName = :loginName or o.mobile = :loginName)";
        }
        if (StringUtils.isNotEmpty(entityNo)) {
            hql += " and o.entityNo = :entityNo ";
        }
        if (StringUtils.isNotEmpty(uuid)) {
            hql += " and o.uuid != :uuid ";
        }
        Query q = getH4Session().createQuery(hql);
        if (StringUtils.isNotEmpty(loginName)) {
            q.setString("loginName", loginName);
        }
        if (StringUtils.isNotEmpty(entityNo)) {
            q.setString("entityNo", entityNo);
        }
        if (StringUtils.isNotEmpty(uuid)) {
            q.setString("uuid", uuid);
        }
        List list = q.list();
        if (list != null && list.size() > 0) {
            return true;
        }
        return false;
    }

    /**
     * 商户后台 - 查询会员列表（连表查询）
     */
    @Override
    public List<CustomerPO> queryCustomerList(QueryMessageDTO qm, boolean byPage) {
        String hql = "select o.uuid,o.entityNo,o.loginName,o.password,o.mobile,o.source,o.activeState,o.createTime";
        hql += ",oa.uuid,oa.nickname,oa.sex,oa.birthday,oa.useIntegral,oa.lastLoginTime";
        hql += " from CustomerPO o , CustomerAccountPO oa where o.uuid = oa.customerUuid ";

        hql += prepareHql(qm);
        hql += getAppendHql(qm);

        Query q = getH4Session().createQuery(hql);
        setValue(qm, q);
        setAppendHqlValue(qm, q);

        if (byPage) {
            q.setFirstResult(qm.getStartNum());
            q.setMaxResults(qm.getPageShow());
        }

        List<Object[]> tempList = q.list();
        return exeResultList(tempList);
    }

    /**
     * 商户后台 - 查询会员数量（连表查询）
     */
    @Override
    public int queryCustomerCount(QueryMessageDTO qm) {
        String hql = "select count(o.uuid) ";
        hql += " from CustomerPO o , CustomerAccountPO oa where o.uuid = oa.customerUuid ";

        hql += prepareHql(qm);
        hql += getAppendHql(qm);

        Query q = getH4Session().createQuery(hql);
        setValue(qm, q);
        setAppendHqlValue(qm, q);

        return ((Number) q.uniqueResult()).intValue();
    }

    /**
     * 根据商户id查询商户的会员
     *
     * @param queryMessage 统一查询条件 {@link QueryMessageDTO}
     * @param byPage   是否分页
     * @return 信息列表 {@link List}&lt;{@link Map}&lt;{@link String}, {@link Object}&gt&gt;
     */
    @Override
    public List<Map<String, Object>> queryCustomerByVendorId(QueryMessageDTO queryMessage, boolean byPage) {
        String vendorId = MapUtils.getString(queryMessage.getQueryParams().get("vendorId"), "value");
        String queryStr = MapUtils.getString(queryMessage.getQueryParams().get("query"), "value");
        String sql = "SELECT cus.uuid, cus.mobile, cus.create_time, acc.nickname, acc.image, csm.time, csm.money, sco.usable_integral,acc.remark"
                + " FROM cus_customer cus"
                + " LEFT JOIN cus_customer_account acc ON cus.uuid = acc.customer_uuid"
                + " LEFT JOIN cater_consume_statistics csm ON csm.vendor_uuid = cus.vendor_id AND csm.customer_uuid = cus.uuid"
                + " LEFT JOIN cus_customer_integral sco ON sco.vendor_uuid = cus.vendor_id AND sco.customer_uuid = cus.uuid"
                + " WHERE cus.vendor_id = :vendorId";
        if (StringUtils.isNotBlank(queryStr)) {
            sql += " AND (cus.mobile LIKE :query OR acc.nickname LIKE :query)";
        }
        sql += " ORDER BY csm.money DESC";
        Query query = getH4Session().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (byPage) {
            query.setFirstResult(queryMessage.getStartNum());
            query.setMaxResults(queryMessage.getPageShow());
        }
        query.setString("vendorId", vendorId);
        if (StringUtils.isNotBlank(queryStr)) {
            query.setString("query", "%" + queryStr + "%");
        }
        return query.list();
    }

    /**
     * 根据商户id查询商户的会员的总数
     *
     * @param queryMessage 统一查询条件 {@link QueryMessageDTO}
     * @return 商户的会员总数
     */
    @Override
    public int getCustomerCountByVendorId(QueryMessageDTO queryMessage) {
        String vendorId = MapUtils.getString(queryMessage.getQueryParams().get("vendorId"), "value");
        String queryStr = MapUtils.getString(queryMessage.getQueryParams().get("query"), "value");
        String sql = "SELECT count(cus.uuid)"
                + " FROM cus_customer cus"
                + " LEFT JOIN cus_customer_account acc ON cus.uuid = acc.customer_uuid"
                + " LEFT JOIN cater_consume_statistics csm ON csm.vendor_uuid = cus.vendor_id AND csm.customer_uuid = cus.uuid"
                + " LEFT JOIN cus_customer_integral sco ON sco.vendor_uuid = cus.vendor_id AND sco.customer_uuid = cus.uuid"
                + " WHERE cus.vendor_id = :vendorId";
        if (StringUtils.isNotBlank(queryStr)) {
            sql += " AND (cus.mobile LIKE :query OR acc.nickname LIKE :query)";
        }
        Query query = getH4Session().createSQLQuery(sql);
        query.setString("vendorId", vendorId);
        if (StringUtils.isNotBlank(queryStr)) {
            query.setString("query", "%" + queryStr + "%");
        }
        return ((Number) query.uniqueResult()).intValue();
    }

    /**
     * 如果需要从多个表中获取多项数据，这个方法用来执行对结果集的处理
     *
     * @param tempList 查询获得的数据
     * @return 把查询获得的数据，通过处理，转换成最终需要的数据
     */
    @Override
    protected List<CustomerPO> exeResultList(List<Object[]> tempList) {
        List<CustomerPO> list = new ArrayList<CustomerPO>();
        if (tempList != null && tempList.size() > 0) {
            CustomerPO customer = null;
            CustomerAccountPO customerAccount = null;
            for (Object[] object : tempList) {
                customer = new CustomerPO();
                customerAccount = new CustomerAccountPO();
                customer.setUuid((String) object[0]);
                customer.setEntityNo((String) object[1]);
                customer.setLoginName((String) object[2]);
                customer.setPassword((String) object[3]);
                customer.setMobile((String) object[4]);
                customer.setSource((String) object[5]);
                customer.setActiveState((String) object[6]);
                customer.setCreateTime((String) object[7]);
                customerAccount.setUuid((String) object[8]);
                customerAccount.setNickname((String) object[9]);
                customerAccount.setSex((int) object[10]);
                customerAccount.setBirthday((String) object[11]);
                customerAccount.setUseIntegral((Integer) object[12]);
                customerAccount.setLastLoginTime((String) object[13]);
                customer.setCustomerAccount(customerAccount);
                list.add(customer);
            }
        }
        return list;
    }

    
    /**
     * 根据时间统计商户的会员数
     * @param date
     * @param vendorUuid
     * @return
     */
	@Override
	public int getCustomerCountByDate(String date, String vendorUuid) {
        String sql = "SELECT count(cus.uuid)"
                + " FROM cus_customer cus"
                + " LEFT JOIN cus_vendor_customer_rel rel ON cus.uuid = rel.customer_id"
                + " WHERE rel.vendor_id = :vendorId and substr(cus.create_time,1,10) <= :date";

        Query q = getH4Session().createSQLQuery(sql);
        q.setParameter("vendorId", vendorUuid);
        q.setParameter("date", date);

        Object object = q.uniqueResult();
        if (object != null) {
        	BigInteger integer = (BigInteger)object;
        	return integer.intValue();
        }
        
        return 0;
	}

}
